package com.dy.yunying.biz.utils;

import com.alibaba.excel.util.FileUtils;
import com.dy.yunying.api.utils.RandomUtils;
import com.sjda.framework.common.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.*;

/**
 * @author cx
 * @version 1.0.0
 * @ClassName GenerateVoucherUtils.java
 * @createTime 2021年11月16日 16:03:00
 */
public class GenerateVoucherUtils {
	/**
	 * POI 读取Excel文件
	 * @author yangtingting
	 * @date 2019/07/29
	 */
	public static void main(String[] args) throws Exception {
		//代金券生成
//		generateCdk();
		//发送消息生成
		generateMsg();
	}
	/**
	 * 代金券生成
	 */
	public static void generateCdk() throws Exception{
		//创建Excel，读取文件内容
		File file=new File("D:/Work/代金券活动发放名单/代金券活动发放名单 2021-11-23.xls");
		HSSFWorkbook workbook=new HSSFWorkbook(FileUtils.openInputStream(file));
		//两种方式读取工作表
		HSSFSheet sheet=workbook.getSheetAt(0);
		//获取sheet中最后一行行号
		int lastRowNum = sheet.getLastRowNum();
		for (int i=1;i<=lastRowNum;i++){
			StringBuffer sql = new StringBuffer();
			sql.append("INSERT INTO wan_cdk (cdk_code,cdk_name,money,belongs_id,create_time,give_time,create_user_id,create_user_name,expiry_type,start_time,end_time,remark,use_status,cdk_type,limit_money_type,limit_money,freeze_time) VALUES ");
			String cdk_name = "";
			String money = "";
			String username = "";
			String expiry_type = "2";
			String start_time = "";
			String end_time = "";
			String remark = "";
			String limit_money_type = "2";
			String limit_money = "";
			String create_user_name = "";

			HSSFRow row = sheet.getRow(i);
			//获取当前行最后单元格列号
			int lastCellNum=row.getLastCellNum();
			for (int j=0;j<lastCellNum;j++){
				HSSFCell cell=row.getCell(j);
				if (Objects.isNull(cell)){
					continue;
				}
				//设置单元格类型
				cell.setCellType(CellType.STRING);
				String value = cell.getStringCellValue();

				switch (j) {
					case 0 :
						money = value;
					case 1 :
						limit_money = value;
					case 2 :
						cdk_name = value;
					case 3 :
					case 4 :
						start_time = value;
					case 5 :
						end_time = value;
					case 6 :
						username = value;
					case 7 :
						create_user_name = value;
					case 8 :
						remark = value;
					default:
				}
			}
			String cdk = getCdkNum();
			sql.append("('"+cdk+"','"+cdk_name+"',"+money+",(SELECT userid FROM wan_user where username = '"+username+"'),SYSDATE(),SYSDATE(),0,'"+create_user_name+"',"+expiry_type+",'"+start_time+"','"+end_time+"','"+remark+"',10,4,"+limit_money_type+","+limit_money+",0);");
			System.out.println(sql);
		}
	}

	private static String insertMSg = "INSERT INTO wan_site_msg (mtitle,mcontent,pushtime,createuser,createtime) VALUES ('资深玩家感恩回馈','感谢您对3367游戏的支持，活动专属代金券已经发送至您的账户，可在“账户-代金券”中查看，充值时选择使用，祝您游戏快乐！',SYSDATE(),0,SYSDATE());";
	/**
	 * 发送消息生成
	 */
	public static void generateMsg(){
		// TODO 发送消息 下面三个值必改
		String msgId = "44686";
		String title = "资深玩家感恩回馈";
		String content = "感谢您对3367游戏的支持，活动专属代金券已经发送至您的账户，可在“账户-代金券”中查看，充值时选择使用，祝您游戏快乐！";

		File file = new File("D:/Work/代金券活动发放名单/2021-11-23代金券发放名单.txt");

		List<String> list = readFileContent(file);
		list.forEach(item ->{
			StringBuffer sql = new StringBuffer();
			sql.append("INSERT INTO wan_site_msg_info (mid,adminid,senderid,receiveverid,title,mstxt,createtime,updatetime,status,msgtype,receivetype,pushtime) VALUES ("+msgId+",0,0,(SELECT userid FROM wan_user where username = '"+item+"'),'"+title+"','"+content+"',SYSDATE(),SYSDATE(),0,2,0,SYSDATE());");
			System.out.println(sql);
		});
	}

	/**
	 * 获取txt文本内容 （获取礼包码）
	 * @param file
	 * @return
	 * @throws Exception
	 */
	public static List<String> readFileContent(File file) {
		BufferedReader reader = null;
		List<String> list = new ArrayList<>();
		try {
			reader = new BufferedReader(new FileReader(file));
			String tempStr;
			while ((tempStr = reader.readLine()) != null) {
				tempStr = tempStr.trim();
				if (StringUtils.isNotBlank(tempStr) && tempStr.length() > 0) {
					list.add(tempStr);
				}
			}
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
		}
		return list;
	}
	private static final Set<String> cdkSet = new HashSet<>();

	public static String getCdkNum() {
		String cdkStr = null;
		do {
			cdkStr = RandomUtils.getCdkNum();
		} while (cdkSet.contains(cdkStr));

		cdkSet.add(cdkStr);
		return cdkStr;
	}
}
